package qingxin.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import qingxin.dao.NewsCommentDAO;
import qingxin.dbc.DatabaseConnection;
import qingxin.vo.NewsComment;

public class NewsCommentDAOImpl implements NewsCommentDAO {

	@Override
	public NewsComment[] getByNewsID(int newsID) throws Exception {
		ArrayList<NewsComment> comments = new ArrayList<NewsComment>(); 
		String sql = "select ncid, nc_uid, datetime, content from news_comment where nc_nid = ? " +
				"order by datetime desc";
		DatabaseConnection dbc = null;
		try{
			dbc =  new DatabaseConnection();
			PreparedStatement pstmt = dbc.getConnection().prepareStatement(sql);
			pstmt.setInt(1, newsID);
			
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				NewsComment comment = new NewsComment();
				comment.setNewsCommentID(rs.getInt(1));
				comment.setUserID(rs.getInt(2));
				comment.setNewsID(newsID);
				comment.setDate(rs.getTimestamp(3).toString());
				comment.setContent(rs.getString(4));
				
				comments.add(comment);
			}
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
		return comments.toArray(new NewsComment[0]);
	}

	@Override
	public int getNumberByNewsID(int newsID) throws Exception {
		int number = 0;
		String sql = "select count(*) from news_comment where nc_nid = ?";
		DatabaseConnection dbc = null;
		try{
			dbc = new DatabaseConnection();
			PreparedStatement pstmt = dbc.getConnection().prepareStatement(sql);
			pstmt.setInt(1, newsID);
			
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				number = rs.getInt(1);
			}
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
		return number;
	}

	@Override
	public void add(int newsID, int userID, String content, String datetime) throws Exception{
		String sql = "insert into news_comment (nc_nid, nc_uid, datetime, content) values (?,?,?,?)";
		DatabaseConnection dbc = null;
		try{
			dbc = new DatabaseConnection();
			PreparedStatement pstmt = dbc.getConnection().prepareStatement(sql);
			
			pstmt.setInt(1, newsID);
			pstmt.setInt(2, userID);
			pstmt.setString(3, datetime);
			pstmt.setString(4, content);
			
			pstmt.executeUpdate();
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
	}

	@Override
	public void delete(int newsCommentID) throws Exception{
		String sql = "delete from news_comment where ncid = ?";
		DatabaseConnection dbc = null;
		try{
			dbc = new DatabaseConnection();
			PreparedStatement pstmt = dbc.getConnection().prepareStatement(sql);
			
			pstmt.setInt(1, newsCommentID);
			pstmt.executeUpdate();
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
		
	}

	@Override
	public NewsComment[] getAll() throws Exception {
		ArrayList<NewsComment> comments = new ArrayList<NewsComment>(); 
		String sql = "select ncid, nc_uid, datetime, content,nc_nid from news_comment " +
				"order by datetime desc";
		DatabaseConnection dbc = null;
		try{
			dbc =  new DatabaseConnection();
			PreparedStatement pstmt = dbc.getConnection().prepareStatement(sql);
			
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				NewsComment comment = new NewsComment();
				comment.setNewsCommentID(rs.getInt(1));
				comment.setUserID(rs.getInt(2));
				comment.setNewsID(rs.getInt(5));
				comment.setDate(rs.getTimestamp(3).toString());
				comment.setContent(rs.getString(4));
				
				comments.add(comment);
			}
		}
		catch(Exception e){
			throw e;
		}
		finally{
			dbc.close();
		}
		return comments.toArray(new NewsComment[0]);
	}

}
